use SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdContactDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdContactDetails
	END
GO

CREATE Procedure [dbo].[InsUpdContactDetails] (
	@CONTACTNO				VARCHAR(10)		= '0'
,	@CONTACTGUID			VARCHAR(100)	
,	@LASTNAME				VARCHAR(200)	= ''
,	@FIRSTNAME				VARCHAR(200)	= ''
,	@STREET1				VARCHAR(100)	= ''
,	@STREET2				VARCHAR(100)	= ''
,	@CITY					VARCHAR(100)	= ''
,	@STATE					VARCHAR(100)	= ''
,	@ZIP					VARCHAR(100)	= ''
,	@JOBTITLE				VARCHAR(200) 	= ''
,	@DEPARTMENT				VARCHAR(200) 	= ''
,	@PHONE					VARCHAR(100) 	= ''
,	@FAX					VARCHAR(100) 	= ''
,	@CELLPHONE				VARCHAR(100) 	= ''
,	@EMAIL					VARCHAR(200) 	= ''
,	@NOTEID					VARCHAR(10)	 	= ''
,	@NOTES					VARCHAR(max) 	= ''
,	@ISACTIVE				VARCHAR(10)	 	= '1'
,	@ADDRESSNO				VARCHAR(10)		= ''
,	@ADDRESSGUID			VARCHAR(100) 	= ''
,	@COMMUNICATIONID		VARCHAR(10)	 	= ''
,	@EMAILID				VARCHAR(10)	 	= ''
,	@USERID					VARCHAR(10)		= '1'
-- BELOW FIELDS GOES INTO TR_RELATIONSHIP TABLE
,	@OWNERID				VARCHAR(10)		= '1'
,	@ISMAINCONTACT			VARCHAR(10)		= ''
,	@RELATEDPARTNERNO		VARCHAR(10)		= ''
)
AS
BEGIN

	-- ***********************************************
	-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
	-- START: DONOT MODIFY THE BELOW CODE
	-- ***********************************************
	DECLARE
		@PARTNERDIVISIONID		VARCHAR(10)
	,	@ADDRESSTYPE			VARCHAR(10)
	,	@PHONEEXTENSION			VARCHAR(10)
	,	@NOTETYPE				VARCHAR(10)

	SELECT
		@PARTNERDIVISIONID		= '1'
	,	@ADDRESSTYPE			= '2'
	,	@PHONEEXTENSION			= ''
	,	@NOTETYPE				= '3'
	-- ***********************************************
	-- END: DONOT MODIFY THE BELOW CODE
	-- ***********************************************


	DECLARE @C_PARTNERCATEGORY	INT
	DECLARE @C_PARTNERTYPE		INT
	
	SET @C_PARTNERCATEGORY	= 1
	SET @C_PARTNERTYPE		= 3

	SET @ISACTIVE		= CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '' END
	SET @ISMAINCONTACT	= CASE WHEN @ISMAINCONTACT IN ('X','1') THEN '1' ELSE '' END
	
	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_PARTNER WHERE PartnerGUID = @CONTACTGUID) 
			BEGIN		-- UPDATE
				UPDATE TR_PARTNER
				SET	PartnerLastName		= @LASTNAME
				,	PartnerFirstName	= @FIRSTNAME
				,	PartnerDivision		= @PARTNERDIVISIONID
				,	isActive			= @ISACTIVE
				,	ChangedBy			= @USERID
				,	ChangeDate			= GETDATE()
				WHERE
					PartnerNo			= @CONTACTNO
				AND	PartnerGuid			= @CONTACTGUID
				AND	PartnerCategory		= @C_PARTNERCATEGORY
				AND	PartnerType			= @C_PARTNERTYPE

				IF ( ISNULL(@ADDRESSGUID,'') = '')
					SELECT @ADDRESSGUID	= CAST(NEWID() AS VARCHAR(100))

			END
			ELSE
			BEGIN		-- INSERT
				SELECT @CONTACTNO	= ISNULL(MAX(PartnerNo), 0) +1 FROM TR_PARTNER
				SELECT @ADDRESSGUID		= CAST(NEWID() AS VARCHAR(100))

				-- PARTNER

				INSERT INTO TR_PARTNER (PartnerNo, PartnerGUID, PartnerCategory, PartnerType, 
										PartnerLastName, PartnerFirstName,
										PartnerDivision, isActive, 
										CreatedBy, CreateDate, ChangedBy, ChangeDate)
				VALUES(
					@CONTACTNO				-- PartnerNo
				,	@CONTACTGUID			-- PartnerGUID
				,	@C_PARTNERCATEGORY		-- PartnerCategory
				,	@C_PARTNERTYPE			-- PartnerType
				,	@LASTNAME				-- PartnerLastName
				,	@FIRSTNAME				-- PartnerFirstName
				,	@PARTNERDIVISIONID		-- PartnerDivision
				,	@ISACTIVE				-- isActive
				,	@USERID					-- CreatedBy
				,	GetDate()				-- CreateDate
				,	@USERID					-- ChangedBy
				,	GetDate()				-- ChangeDate
				)
			END

			

			if EXISTS(SELECT 1 FROM TR_CONTACT WHERE ContactGUID = @CONTACTGUID)
			BEGIN
				UPDATE TR_CONTACT
				SET JobTitle = @JOBTITLE
				,	Department = @DEPARTMENT
				,	ChangedBy = @USERID
				,	ChangeDate = GETDATE()
				WHERE
					ContactGUID = @CONTACTGUID
			END
			ELSE
			BEGIN
				INSERT INTO TR_CONTACT (PartnerNo, ContactGUID, JobTitle, Department, CreatedBy, CreateDate, ChangedBy, ChangeDate)
				VALUES(
					@CONTACTNO		-- PartnerNo
				,	@CONTACTGUID	-- ContactGUID
				,	@JOBTITLE		-- JobTitle
				,	@DEPARTMENT		-- Department
				,	@USERID			-- CreatedBy		= 
				,	GETDATE()		-- CreateDate
				,	@USERID			-- ChangedBy
				,	GETDATE()		-- ChangeDate
				)
			END



			exec InsUpdAddress 
				@ADDRESSGUID	= @ADDRESSGUID
			,	@PARTNERGUID	= @CONTACTGUID
			,	@ADDRESSNO		= @ADDRESSNO
			,	@STREET1		= @STREET1
			,	@STREET2		= @STREET2
			,	@CITY			= @CITY
			,	@STATE			= @STATE
			,	@ZIP			= @ZIP
			,	@USERID			= @USERID

			exec InsUpdCommunication
				@ADDRESSGUID		= @ADDRESSGUID
			,	@PARTNERGUID		= @CONTACTGUID
			,	@COMMUNICATIONID	= @COMMUNICATIONID
			,	@PHONE				= @PHONE
			,	@PHONEEXTENSION		= @PHONEEXTENSION
			,	@FAX				= @FAX
			,	@CELLPHONE			= @CELLPHONE
			,	@USERID				= @USERID

			exec InsUpdEmail
				@EMAILID			= @EMAILID
			,	@PARTNEREMAIL		= @EMAIL
			,	@ADDRESSTYPE		= @ADDRESSTYPE
			,	@PARTNERGUID		= @CONTACTGUID
			,	@USERID				= @USERID

			exec InsUpdNotes
				@NOTEID				= @NOTEID
			,	@NOTESOURCEGUID		= @CONTACTGUID
			,	@SOURCEID			= @CONTACTNO
			,	@NOTE				= @NOTES
			,	@USERID				= @USERID
			,	@NOTETYPE			= @NOTETYPE


			IF ( CAST(ISNULL(@OWNERID,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @CONTACTGUID
				,	@PARTNERID	= @OWNERID
				,	@RELATIONSHIPTYPE = 3 -- OWNER
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END


			IF ( CAST(ISNULL(@RELATEDPARTNERNO,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @CONTACTGUID
				,	@PARTNERID	= @RELATEDPARTNERNO
				,	@RELATIONSHIPTYPE = 8	-- ACCOUNT 
				,	@ISMAIN		= @ISMAINCONTACT
				,	@USERID		= @USERID
			END


			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Contact Details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Contact Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
